libname hcup2009 '/homes/data/hcup/sedd/2009';
libname seddwork '/disk/data3/sedd.work/millers/2009';

proc sql;
create table seddwork.totalall as select 
sum(public*cost) as publicc,
sum(private*cost) as privatec,
sum(self*cost) as selfc,
sum(freecare*cost) as freecarec,
sum(Chronic) as HCUPChronic,
sum(ecode) as ecode,
sum(cost) as cost,
sum(TOTCHG) as charges,
sum(infect) as infect,
sum(neoplasms) as neoplasms,
sum(endometa) as endometa,
sum(blood) as blood,
sum(mentaldisorders) as mentaldisorders,
sum(nervoussys) as nervoussys,
sum(sense) as sense,
sum(circulatory) as circulatory,
sum(respiratory) as respiratory,
sum(digestive) as digestive,
sum(genitourinary) as genitourinary,
sum(skin) as skin,
sum(muscle) as muscle,
sum(congenital) as congenital,
sum(perinatal) as perinatal,
sum(illdefined) as illdefined,
sum(injpois) as injpois,
sum(Appendicitis) as Appendicitis,
sum(injury) as injury,
sum(fracture) as fracture,
sum(mental) as mental,
sum(preg) as preg,
sum(count) as count,
bmonth as bmonth,
byear as byear
from seddwork.sedd2009
group by bmonth, byear;
quit;

proc sql;
create table seddwork.totalblack as select 
sum(public*cost) as publicc,
sum(private*cost) as privatec,
sum(self*cost) as selfc,
sum(freecare*cost) as freecarec,
sum(Chronic) as HCUPChronic,
sum(ecode) as ecode,
sum(cost) as cost,
sum(TOTCHG) as charges,
sum(infect) as infect,
sum(neoplasms) as neoplasms,
sum(endometa) as endometa,
sum(blood) as blood,
sum(mentaldisorders) as mentaldisorders,
sum(nervoussys) as nervoussys,
sum(sense) as sense,
sum(circulatory) as circulatory,
sum(respiratory) as respiratory,
sum(digestive) as digestive,
sum(genitourinary) as genitourinary,
sum(skin) as skin,
sum(muscle) as muscle,
sum(congenital) as congenital,
sum(perinatal) as perinatal,
sum(illdefined) as illdefined,
sum(injpois) as injpois,
sum(Appendicitis) as Appendicitis,
sum(injury) as injury,
sum(fracture) as fracture,
sum(mental) as mental,
sum(preg) as preg,
sum(count) as count,
bmonth as bmonth,
byear as byear
from seddwork.sedd2009
where race=2 
group by bmonth, byear;
quit;


proc sql;
create table seddwork.totalnotblack as select 
sum(public*cost) as publicc,
sum(private*cost) as privatec,
sum(self*cost) as selfc,
sum(freecare*cost) as freecarec,
sum(Chronic) as HCUPChronic,
sum(ecode) as ecode,
sum(cost) as cost,
sum(TOTCHG) as charges,
sum(infect) as infect,
sum(neoplasms) as neoplasms,
sum(endometa) as endometa,
sum(blood) as blood,
sum(mentaldisorders) as mentaldisorders,
sum(nervoussys) as nervoussys,
sum(sense) as sense,
sum(circulatory) as circulatory,
sum(respiratory) as respiratory,
sum(digestive) as digestive,
sum(genitourinary) as genitourinary,
sum(skin) as skin,
sum(muscle) as muscle,
sum(congenital) as congenital,
sum(perinatal) as perinatal,
sum(illdefined) as illdefined,
sum(injpois) as injpois,
sum(Appendicitis) as Appendicitis,
sum(injury) as injury,
sum(fracture) as fracture,
sum(mental) as mental,
sum(preg) as preg,
sum(count) as count,
bmonth as bmonth,
byear as byear
from seddwork.sedd2009
where race NE 2
group by bmonth, byear;
quit;


***By income of zipcode;

proc sql;
create table seddwork.totalallli as select 
sum(public*cost) as publicc,
sum(private*cost) as privatec,
sum(self*cost) as selfc,
sum(freecare*cost) as freecarec,
sum(Chronic) as HCUPChronic,
sum(ecode) as ecode,
sum(cost) as cost,
sum(TOTCHG) as charges,
sum(infect) as infect,
sum(neoplasms) as neoplasms,
sum(endometa) as endometa,
sum(blood) as blood,
sum(mentaldisorders) as mentaldisorders,
sum(nervoussys) as nervoussys,
sum(sense) as sense,
sum(circulatory) as circulatory,
sum(respiratory) as respiratory,
sum(digestive) as digestive,
sum(genitourinary) as genitourinary,
sum(skin) as skin,
sum(muscle) as muscle,
sum(congenital) as congenital,
sum(perinatal) as perinatal,
sum(illdefined) as illdefined,
sum(injpois) as injpois,
sum(Appendicitis) as Appendicitis,
sum(injury) as injury,
sum(fracture) as fracture,
sum(mental) as mental,
sum(preg) as preg,
sum(count) as count,
bmonth as bmonth,
byear as byear
from seddwork.sedd2009 
where ZIPINC_QRTL LT 2 
group by bmonth, byear;
quit;

proc sql;
create table seddwork.totalblackli as select 
sum(public*cost) as publicc,
sum(private*cost) as privatec,
sum(self*cost) as selfc,
sum(freecare*cost) as freecarec,
sum(Chronic) as HCUPChronic,
sum(ecode) as ecode,
sum(cost) as cost,
sum(TOTCHG) as charges,
sum(infect) as infect,
sum(neoplasms) as neoplasms,
sum(endometa) as endometa,
sum(blood) as blood,
sum(mentaldisorders) as mentaldisorders,
sum(nervoussys) as nervoussys,
sum(sense) as sense,
sum(circulatory) as circulatory,
sum(respiratory) as respiratory,
sum(digestive) as digestive,
sum(genitourinary) as genitourinary,
sum(skin) as skin,
sum(muscle) as muscle,
sum(congenital) as congenital,
sum(perinatal) as perinatal,
sum(illdefined) as illdefined,
sum(injpois) as injpois,
sum(Appendicitis) as Appendicitis,
sum(injury) as injury,
sum(fracture) as fracture,
sum(mental) as mental,
sum(preg) as preg,
sum(count) as count,
bmonth as bmonth,
byear as byear
from seddwork.sedd2009
where race=2 and ZIPINC_QRTL LT 2 
group by bmonth, byear;
quit;


proc sql;
create table seddwork.nonblackli as select 
sum(public*cost) as publicc,
sum(private*cost) as privatec,
sum(self*cost) as selfc,
sum(freecare*cost) as freecarec,
sum(Chronic) as HCUPChronic,
sum(ecode) as ecode,
sum(cost) as cost,
sum(TOTCHG) as charges,
sum(infect) as infect,
sum(neoplasms) as neoplasms,
sum(endometa) as endometa,
sum(blood) as blood,
sum(mentaldisorders) as mentaldisorders,
sum(nervoussys) as nervoussys,
sum(sense) as sense,
sum(circulatory) as circulatory,
sum(respiratory) as respiratory,
sum(digestive) as digestive,
sum(genitourinary) as genitourinary,
sum(skin) as skin,
sum(muscle) as muscle,
sum(congenital) as congenital,
sum(perinatal) as perinatal,
sum(illdefined) as illdefined,
sum(injpois) as injpois,
sum(Appendicitis) as Appendicitis,
sum(injury) as injury,
sum(fracture) as fracture,
sum(mental) as mental,
sum(preg) as preg,
sum(count) as count,
bmonth as bmonth,
byear as byear
from seddwork.sedd2009
where race NE 2 and ZIPINC_QRTL LT 2 
group by bmonth, byear;
quit;

********by state;

proc sql;
create table seddwork.allerbystate as select 
sum(Chronic) as HCUPChronic,
sum(ecode) as ecode,
sum(cost) as cost,
sum(TOTCHG) as charges,
sum(infect) as infect,
sum(neoplasms) as neoplasms,
sum(endometa) as endometa,
sum(blood) as blood,
sum(mentaldisorders) as mentaldisorders,
sum(nervoussys) as nervoussys,
sum(sense) as sense,
sum(circulatory) as circulatory,
sum(respiratory) as respiratory,
sum(digestive) as digestive,
sum(genitourinary) as genitourinary,
sum(skin) as skin,
sum(muscle) as muscle,
sum(congenital) as congenital,
sum(perinatal) as perinatal,
sum(illdefined) as illdefined,
sum(injpois) as injpois,
sum(Appendicitis) as Appendicitis,
sum(injury) as injury,
sum(fracture) as fracture,
sum(mental) as mental,
sum(preg) as preg,
sum(count) as count,
bmonth as bmonth,
byear as byear,
fips as fips

from seddwork.sedd2009
group by bmonth, byear, fips;
quit;

proc sql;
create table seddwork.blackerbystate as select 
sum(Chronic) as HCUPChronic,
sum(ecode) as ecode,
sum(cost) as cost,
sum(TOTCHG) as charges,
sum(infect) as infect,
sum(neoplasms) as neoplasms,
sum(endometa) as endometa,
sum(blood) as blood,
sum(mentaldisorders) as mentaldisorders,
sum(nervoussys) as nervoussys,
sum(sense) as sense,
sum(circulatory) as circulatory,
sum(respiratory) as respiratory,
sum(digestive) as digestive,
sum(genitourinary) as genitourinary,
sum(skin) as skin,
sum(muscle) as muscle,
sum(congenital) as congenital,
sum(perinatal) as perinatal,
sum(illdefined) as illdefined,
sum(injpois) as injpois,
sum(Appendicitis) as Appendicitis,
sum(injury) as injury,
sum(fracture) as fracture,
sum(mental) as mental,
sum(preg) as preg,
sum(count) as count,
bmonth as bmonth,
byear as byear,
fips as fips
from seddwork.sedd2009
where race=2
group by bmonth, byear, fips;
quit;


proc sql;
create table seddwork.notblackerbystate as select 
sum(Chronic) as HCUPChronic,
sum(ecode) as ecode,
sum(cost) as cost,
sum(TOTCHG) as charges,
sum(infect) as infect,
sum(neoplasms) as neoplasms,
sum(endometa) as endometa,
sum(blood) as blood,
sum(mentaldisorders) as mentaldisorders,
sum(nervoussys) as nervoussys,
sum(sense) as sense,
sum(circulatory) as circulatory,
sum(respiratory) as respiratory,
sum(digestive) as digestive,
sum(genitourinary) as genitourinary,
sum(skin) as skin,
sum(muscle) as muscle,
sum(congenital) as congenital,
sum(perinatal) as perinatal,
sum(illdefined) as illdefined,
sum(injpois) as injpois,
sum(Appendicitis) as Appendicitis,
sum(injury) as injury,
sum(fracture) as fracture,
sum(mental) as mental,
sum(preg) as preg,
sum(count) as count,
bmonth as bmonth,
byear as byear,
fips as fips
from seddwork.sedd2009
where race NE 2
group by bmonth, byear, fips;
quit;


